﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Clinica_Frba.CompradeBono
{
    public partial class Compra : Form
    {
        public Compra()
        {
            InitializeComponent();
        }
        bool encompra;
        private void Compra_Load(object sender, EventArgs e)
        {
            encompra= false;
            label1.Text = "ingrese compra";
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            string comando;
            comando = "SELECT id_afil,Apellido,Nombre,Descripcion FROM DUNDER_MIFFLIN.AFILIADO LEFT JOIN DUNDER_MIFFLIN.PLAN_MEDICO ON DUNDER_MIFFLIN.PLAN_MEDICO.ID_PLAN=DUNDER_MIFFLIN.AFILIADO.ID_PLAN WHERE ESTADO='H'";
            SqlCommand consulta = new SqlCommand(comando, myConnection);
            SqlDataReader da = consulta.ExecuteReader();
            DataTable dt = new DataTable();
            while (da.Read())
            {
              listBox1.Items.Add(da.GetString(0)+'-'+da.GetString(1)+'-'+da.GetString(2)+'-'+da.GetString(3)); 
            }
            listBox1.SelectedIndex = 0;
            myConnection.Close();
            nrocompra = 0;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int index=listBox1.GetItemText(listBox1.SelectedItem).IndexOf('-');
            string afiliadod;
            afiliadod = listBox1.GetItemText(listBox1.SelectedItem).Substring(0,index);
            
            int cant;
            if (Int32.TryParse(textBox1.Text, out cant)) { } else { cant = 1; }
            if (encompra == false) { return; }
            string f = publico.fecha;
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            int ibono, iplan, i, idm;

            for (i = 0; i < cant; i++)
            {
                try
                {
                    SqlCommand ini = new SqlCommand("BEGIN TRAN BF", myConnection);
                    ini.ExecuteNonQuery();
                    SqlCommand idb = new SqlCommand("SELECT ULTIMOID FROM DUNDER_MIFFLIN.PROVIDERID WHERE TABLA='BONO_FARMACIA'", myConnection);
                    ibono = Convert.ToInt32(idb.ExecuteScalar());
                    ibono++;
                    SqlCommand idp = new SqlCommand("SELECT ID_PLAN FROM DUNDER_MIFFLIN.AFILIADO WHERE ID_AFIL='" + afiliadod +"'", myConnection);
                    iplan = Convert.ToInt32(idp.ExecuteScalar());
                    string afiliado = afiliadod;
                    string fecha = publico.fecha;
                    SqlCommand com = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.BONO_FARMACIA(ID_BONO,GRUPO_FAMILIAR,ID_PLAN,USADO,FECHA_COMPRA,FECHA_IMPRESION,FECHA_VENCIMIENTO) VALUES(" + ibono + ",LEFT('" + afiliado + "',LEN('" + afiliado + "')-2)," + iplan + ",'N',CONVERT(datetime,'" + fecha + "',103),CONVERT(datetime,'" + f + "',103),DATEADD(day,60,CONVERT(datetime,'" + fecha + "',103)))", myConnection);
                    com.ExecuteNonQuery();
                    SqlCommand updt = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + ibono + " WHERE TABLA='BONO_FARMACIA'", myConnection);
                    updt.ExecuteNonQuery();
                    SqlCommand bc = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.BONO_COMPRA(ID_BONO_F,ID_COMPRA)VALUES(" + ibono + "," + nrocompra + ")", myConnection);
                    bc.ExecuteNonQuery();

                    SqlCommand plan = new SqlCommand("SELECT DESCRIPCION FROM DUNDER_MIFFLIN.PLAN_MEDICO WHERE ID_PLAN=" + iplan, myConnection);
                    SqlCommand cmit = new SqlCommand("COMMIT TRAN BF", myConnection);
                    cmit.ExecuteNonQuery();
                    SqlCommand upd = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + ibono + " WHERE TABLA='BONO_FARMACIA'", myConnection);
                    MessageBox.Show("Insercion de bono ok afiliado=" + afiliado + " plan=" + plan.ExecuteScalar() + " fecha de compra=" + fecha, "Bono farmacia");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Bono farmacia");
                    SqlCommand rb = new SqlCommand("ROLLBACK TRAN BF", myConnection);
                    rb.ExecuteNonQuery();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            int index = listBox1.GetItemText(listBox1.SelectedItem).IndexOf('-');
            string afiliado;
            afiliado = listBox1.GetItemText(listBox1.SelectedItem).Substring(0, index);
            
            int cant;
            if (Int32.TryParse(textBox2.Text, out cant)) { } else { cant = 1; }
            string fechacompra = publico.fecha; 
            string f = publico.fecha;
            if (encompra == false) { return; }
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            int ibono,iplan;
            int i;
            for (i = 0; i < cant; i++)
            {
                try
                {
                    SqlCommand ini = new SqlCommand("BEGIN TRAN BC", myConnection);
                    ini.ExecuteNonQuery();
                    SqlCommand idb = new SqlCommand("SELECT ULTIMOID FROM DUNDER_MIFFLIN.PROVIDERID WHERE TABLA='BONO_CONSULTA'", myConnection);
                    ibono = Convert.ToInt32(idb.ExecuteScalar());
                    ibono++;
                    SqlCommand idp = new SqlCommand("SELECT ID_PLAN FROM DUNDER_MIFFLIN.AFILIADO WHERE ID_AFIL='" + afiliado+"'", myConnection);
                    iplan = Convert.ToInt32(idp.ExecuteScalar());
                    string fecha = publico.fecha;
                    SqlCommand com = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.BONO_CONSULTA(ID_BONO,GRUPO_FAMILIAR,ID_PLAN,USADO,FECHA_COMPRA,FECHA_IMPRESION) VALUES(" + ibono + ",LEFT('" + afiliado + "',LEN('" + afiliado + "')-2)," + iplan + ",'N',CONVERT(datetime,'" + fecha + "',103),CONVERT(datetime,'" + fechacompra + "',103))", myConnection);

                    com.ExecuteNonQuery();
                    SqlCommand updt = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + ibono + " WHERE TABLA='BONO_CONSULTA'", myConnection);
                    updt.ExecuteNonQuery();
                    SqlCommand bc = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.BONO_COMPRA(ID_BONO_C,ID_COMPRA)VALUES(" + ibono + "," + nrocompra + ")", myConnection);
                    bc.ExecuteNonQuery();
                    SqlCommand plan = new SqlCommand("SELECT DESCRIPCION FROM DUNDER_MIFFLIN.PLAN_MEDICO WHERE ID_PLAN=" + iplan, myConnection);
                    SqlCommand cmit = new SqlCommand("COMMIT TRAN BC", myConnection);
                    cmit.ExecuteNonQuery();
                    MessageBox.Show("Insercion de bono ok, AFILIADO" + afiliado + " plan=" + plan.ExecuteScalar() + " fecha de compra=" + fecha + " fecha de impresion=" + fechacompra, "Bono consulta");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Bono consulta");
                    SqlCommand rb = new SqlCommand("ROLLBACK TRAN BC", myConnection);
                    rb.ExecuteNonQuery();
                }
            }
        }
        int nrocompra;
        string afiliadoid;
        private void button3_Click(object sender, EventArgs e)
        {
            int index=listBox1.GetItemText(listBox1.SelectedItem).IndexOf('-');
            string afiliado;
            afiliado = listBox1.GetItemText(listBox1.SelectedItem).Substring(0,index);
            if (encompra) { return; }
            encompra = true;

            label1.Text = "en compra";
            afiliadoid = listBox1.GetItemText(listBox1.SelectedItem).Substring(0, index);
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            SqlCommand comp = new SqlCommand("SELECT ULTIMOID FROM DUNDER_MIFFLIN.PROVIDERID WHERE TABLA='COMPRA'", myConnection);
            int idcompra = Convert.ToInt32(comp.ExecuteScalar());
            idcompra++;

            nrocompra = idcompra;
            string fecha = publico.fecha;
            SqlCommand ii = new SqlCommand("SET IDENTITY_INSERT DUNDER_MIFFLIN.COMPRA ON", myConnection);
            ii.ExecuteNonQuery();
            SqlCommand ins = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.COMPRA(ID_COMPRA,ID_AFIL,Fecha,NRO_COMPRA) VALUES(" + nrocompra + ",'" + afiliado + "',CONVERT(datetime,'" + fecha + "',103)," + nrocompra + ")", myConnection);
            ins.ExecuteNonQuery();
            SqlCommand updt = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + nrocompra + " WHERE TABLA='COMPRA'", myConnection);
            updt.ExecuteNonQuery();
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (encompra == false) { return; }
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            int index = listBox1.GetItemText(listBox1.SelectedItem).IndexOf('-');
     
            afiliadoid = listBox1.GetItemText(listBox1.SelectedItem).Substring(0, index);
            
            //SqlCommand serch = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
            //SqlDataReader dr = serch.ExecuteReader();
            //if (dr.HasRows)
            //{
            //    string fecha = publico.fecha;
            //    dr.Close();
            //    SqlCommand ii = new SqlCommand("SET IDENTITY_INSERT DUNDER_MIFFLIN.COMPRA ON", myConnection);
            //    ii.ExecuteNonQuery();
            //    SqlCommand ins = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.COMPRA(ID_COMPRA,ID_AFIL,Fecha,NRO_COMPRA) VALUES(" + nrocompra + ",'" + listBox1.GetItemText(listBox1.SelectedItem) + "'," + fecha + "," + nrocompra + ")", myConnection);
            //    ins.ExecuteNonQuery();
            //    SqlCommand updt = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + nrocompra + " WHERE TABLA='COMPRA'", myConnection);
            //    updt.ExecuteNonQuery();
            //}
            //else
            //{
            //    dr.Close();
            //    SqlCommand del = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
            //    del.ExecuteNonQuery();
            //}
            if (encompra)
            {
                SqlCommand del = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.BONO_FARMACIA WHERE ID_BONO IN (SELECT ID_BONO FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra + ")", myConnection);
                del.ExecuteNonQuery();
                SqlCommand del2 = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.BONO_CONSULTA WHERE ID_BONO IN (SELECT ID_BONO FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra + ")", myConnection);
                del2.ExecuteNonQuery();
                SqlCommand del3 = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
                del3.ExecuteNonQuery();
                SqlCommand del4 = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
                del4.ExecuteNonQuery();
            }
            encompra = false;
            label1.Text = "Ingrese compra";
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (encompra==false){return;}
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            int c, f;
            SqlCommand con = new SqlCommand("SELECT count(*) FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra + " AND ID_BONO_F IS NULL", myConnection);
            c = Convert.ToInt32(con.ExecuteScalar());
            SqlCommand far = new SqlCommand("SELECT count(*) FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra + " AND ID_BONO_C IS NULL", myConnection);
            f = Convert.ToInt32(far.ExecuteScalar());
            SqlCommand com = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
            DataTable dt=new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);



            if (dt.Rows.Count>0) {


                SqlCommand plan = new SqlCommand("SELECT ID_PLAN FROM DUNDER_MIFFLIN.AFILIADO WHERE ID_AFIL='" + afiliadoid + "'", myConnection);
                int pl = Convert.ToInt32(plan.ExecuteScalar());
                SqlCommand precios = new SqlCommand("SELECT PRECIO_CONSULTA,PRECIO_FARMACIA FROM DUNDER_MIFFLIN.PLAN_MEDICO WHERE ID_PLAN=" + pl, myConnection);
                SqlDataReader rdr = precios.ExecuteReader();
                rdr.Read();
                int pf = rdr.GetInt32(1);
                int pc = rdr.GetInt32(0);
                int total = pf * f + pc * c;
                MessageBox.Show("El precio es de "+total,"Compra");
            } else {
                SqlCommand borr = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
                borr.ExecuteNonQuery();
            }
            encompra = false;
            label1.Text = "Ingrese compra";

        }

        private void button5_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(publico.ConnectionString);
            myConnection.Open();
            if (encompra == false) { return; }
            //SqlCommand serch = new SqlCommand("SELECT * FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
            //SqlDataReader dr = serch.ExecuteReader();
            //if (dr.HasRows)
            //{
            //    string fecha = publico.fecha;
            //    dr.Close();
            //    SqlCommand ii = new SqlCommand("SET IDENTITY_INSERT DUNDER_MIFFLIN.COMPRA ON", myConnection);
            //    ii.ExecuteNonQuery();
            //    SqlCommand ins = new SqlCommand("INSERT INTO DUNDER_MIFFLIN.COMPRA(ID_COMPRA,ID_AFIL,Fecha,NRO_COMPRA) VALUES(" + nrocompra + ",'" + afiliadoid + "'," + fecha + "," + nrocompra + ")", myConnection);
            //    ins.ExecuteNonQuery();
            //    SqlCommand updt = new SqlCommand("UPDATE DUNDER_MIFFLIN.PROVIDERID SET ULTIMOID=" + nrocompra + " WHERE TABLA='COMPRA'", myConnection);
            //    updt.ExecuteNonQuery();
            //}
            //else
            //{
            //    dr.Close();
            //    SqlCommand del = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
            //    del.ExecuteNonQuery();
            //}
            if (encompra)
            {
                SqlCommand del = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.BONO_FARMACIA WHERE ID_BONO IN (SELECT ID_BONO FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra + ")", myConnection);
                del.ExecuteNonQuery();
                SqlCommand del2 = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.BONO_CONSULTA WHERE ID_BONO IN (SELECT ID_BONO FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra + ")", myConnection);
                del2.ExecuteNonQuery();
                SqlCommand del3 = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.BONO_COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
                del3.ExecuteNonQuery();
                SqlCommand del4 = new SqlCommand("DELETE FROM DUNDER_MIFFLIN.COMPRA WHERE ID_COMPRA=" + nrocompra, myConnection);
                del4.ExecuteNonQuery();
            }
            encompra = false;
            label1.Text = "Ingrese compra";
    
        }
        
    }
}
